package com.java1234.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class ImportExcelUtils {

	public List<List<String>> getContent(String fileUrl){

		/*String filePath = "123.xlsx";
		
		//定义集合
		List<List<String>> sheetList = new ArrayList<List<String>>();
		 if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx"))
	        {
	            System.out.println("文件不是excel类型");
	        }
		 if(filePath.endsWith(".xls")){
			 try {
			        FileInputStream file= new FileInputStream(filePath);
			        //POIFSFileSystem ts= new POIFSFileSystem(file);
			        HSSFWorkbook wb=new HSSFWorkbook(file);
			        HSSFSheet sh= wb.getSheetAt(0);
			       HSSFRow ro=null;
			        for (int i = 0; sh.getRow(i)!=null; i++) {
			            ro=sh.getRow(i);
			            for (int j = 0; ro.getCell(j)!=null; j++) {
			                System.out.print(ro.getCell(j)+"");
			            }
			            System.out.println();
			        }
			    } catch (Exception e) {
			        e.printStackTrace();
			    }
		 }

		if(filePath.endsWith(".xlsx")){
			try {
				FileInputStream file = new FileInputStream(filePath);
				XSSFWorkbook wb = null;
				try {
					wb = new XSSFWorkbook(file);
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				XSSFSheet sheet = wb.getSheetAt(0);
				int totalRows = sheet.getLastRowNum();
				int rows = sheet.getPhysicalNumberOfRows();
				int columns = sheet.getRow(0).getPhysicalNumberOfCells();
				XSSFRow row = null;
				for(int i= 0;i<totalRows;i++){
					row = sheet.getRow(i);
					 for (int j = 0; row.getCell(j)!=null; j++) {
			                System.out.print(row.getCell(j)+"");
			            }
					
				}
				List<String> colNames= new ArrayList<String>();
				for(int j=0; j<columns; j++ ){
						//Cell cell = sheet.getCell(j, 0);
					Cell cell = (Cell) sheet.getCellComment(0, j);					
					colNames.add(cell.getContents());
					}
	          sheetList.add(colNames);
	        for(int i=0; i<rows ; i++){
		boolean flag = false;
		
		List rowContent = new ArrayList();
		
		for(int j=0; j<colNames.size(); j++ ){
			Cell cell = sheet.getCell(j, i);
			rowContent.add(cell.getContents());
			if(cell.getContents()!=null && !"".equals(cell.getContents())){
				flag = true;
			}
		}
		if(flag){
			sheetList.add(rowContent);
		}
				System.out.println(rows);
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		 
		}

	      return sheetList;
	}*/
		
		InputStream is =null;
		List<List<String>> sheetList = new ArrayList<List<String>>();
		//获得要导入文件
		Workbook wk =null;
		try {
			is = new FileInputStream(fileUrl);
			wk = Workbook.getWorkbook(is);
			//加载到工作薄中
			Sheet sheet= wk.getSheet(0);
			int columns = sheet.getColumns();
			//工作表列数
			int rows = sheet.getRows();
			//工作表行数
			List<String> colNames= new ArrayList<String>();
						for(int j=0; j<columns; j++ ){
				Cell cell = sheet.getCell(j, 0);
				colNames.add(cell.getContents());
			}
			sheetList.add(colNames);
			for(int i=0; i<rows ; i++){
				boolean flag = false;
				
				List rowContent = new ArrayList();
				
				for(int j=0; j<colNames.size(); j++ ){
					Cell cell = sheet.getCell(j, i);
					rowContent.add(cell.getContents());
					if(cell.getContents()!=null && !"".equals(cell.getContents())){
						flag = true;
					}
				}
				if(flag){
					sheetList.add(rowContent);
				}
				
				
			}
			//FileNotFound
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(wk!=null){
				wk.close();
			}
			try{
			 is.close();
			}catch(IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
		
		return sheetList;
	}

	
}
